package com.pcbly.web.persistence;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.Types;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.log4j.Logger;
import org.hibernate.Criteria; 
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projection;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.engine.SessionImplementor;
import org.hibernate.impl.CriteriaImpl;
import org.hibernate.metadata.ClassMetadata;
import org.springframework.orm.ObjectRetrievalFailureException;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.util.Assert;
import org.springframework.util.ReflectionUtils;

import com.pcbly.web.commons.CriteriaSetup;
import com.pcbly.web.components.utils.BeanUtils;
import com.pcbly.web.components.utils.GenericsUtils;
import com.pcbly.web.components.hibernate.support.Page;

@SuppressWarnings("unchecked")
public class HibernateBaseDao<T> extends HibernateDaoSupport implements
		BaseDao<T> {

	/**
	 * 
	 */
	private static final long serialVersionUID = 3781399310981320447L;
	private static Logger _log = Logger.getLogger(HibernateBaseDao.class);

	/**
	 * 去除hql的orderby 子句，用于pagedQuery.
	 * 
	 * @see #pagedQuery(String,int,int,Object[])
	 */
	public static String removeOrders(String hql) {
		Assert.hasText(hql);
		Pattern p = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*",
				Pattern.CASE_INSENSITIVE);
		Matcher m = p.matcher(hql);
		StringBuffer sb = new StringBuffer();
		while (m.find()) {
			m.appendReplacement(sb, "");
		}
		m.appendTail(sb);
		return sb.toString();
	}

	protected CriteriaSetup getDefaultCriteriaSetup() throws Exception {
		return new CriteriaSetup() {
			public void setup(Criteria criteria, Map filter) {
				if (filter != null && !filter.isEmpty()) {
					Set keys = filter.keySet();
					for (Object key : keys) {
						String value = (String) filter.get(key);
						if (StringUtils.isNotBlank(value))
							criteria.add(Restrictions.eq((String) key, value));
					}
				}
			}
		};
	}

	/**
	 * 取得Entity的Criteria.
	 */
	public Criteria getEntityCriteria() throws Exception {
		return getSession().createCriteria(getEntityClass());
	}

	/**
	 * 去除hql的select 子句，未考虑union的情况,用于pagedQuery.
	 * 
	 * @see #pagedQuery(String,int,int,Object[])
	 */
	public static String removeSelect(String hql) throws Exception {
		Assert.hasText(hql);
		int beginPos = hql.toLowerCase().indexOf("from");
		Assert.isTrue(beginPos != -1, " hql : " + hql
				+ " must has a keyword 'from'");
		return hql.substring(beginPos);
	}

	protected Class<T> entityClass;// DAO所管理的Entity类型.

	protected Log logger = LogFactory.getLog(getClass());

	/**
	 * 在构造函数中将泛型T.class赋给entityClass.
	 */

	public HibernateBaseDao() {
		entityClass = GenericsUtils.getSuperClassGenricType(getClass());
	}

	public void clear() throws Exception {
		getHibernateTemplate().clear();
	}

	/**
	 * 创建Criteria对象.
	 * 
	 * @param criterions
	 *            可变的Restrictions条件列表,见{@link #createQuery(String,Object...)}
	 */
	@SuppressWarnings("hiding")
	public <T> Criteria createCriteria(Class<T> entityClass,
			Criterion... criterions) throws Exception {
		Criteria criteria = getSession().createCriteria(entityClass);
		if (criterions != null)
			for (Criterion c : criterions) {
				criteria.add(c);
			}
		return criteria;
	}

	/**
	 * 创建Criteria对象，带排序字段与升降序字段.
	 * 
	 * @see #createCriteria(Class,Criterion[])
	 */
	@SuppressWarnings("hiding")
	public <T> Criteria createCriteria(Class<T> entityClass, String orderBy,
			boolean isAsc, Criterion... criterions) throws Exception {
		Assert.hasText(orderBy);

		Criteria criteria = createCriteria(entityClass, criterions);

		if (isAsc)
			criteria.addOrder(Order.asc(orderBy));
		else
			criteria.addOrder(Order.desc(orderBy));

		return criteria;
	}

	/**
	 * 取得Entity的Criteria.
	 * 
	 * @see HibernateGenericDao#createCriteria(Class,Criterion[])
	 */
	public Criteria createCriteria(Criterion... criterions) throws Exception {
		return createCriteria(getEntityClass(), criterions);
	}

	/**
	 * 取得Entity的Criteria,带排序参数.
	 * 
	 * @see HibernateGenericDao#createCriteria(Class,String,boolean,Criterion[])
	 */
	public Criteria createCriteria(String orderBy, boolean isAsc,
			Criterion... criterions) throws Exception {
		return createCriteria(getEntityClass(), orderBy, isAsc, criterions);
	}

	/**
	 * 创建Query对象.
	 * 对于需要first,max,fetchsize,cache,cacheRegion等诸多设置的函数,可以在返回Query后自行设置.
	 * 留意可以连续设置,如下：
	 * 
	 * <pre>
	 * dao.getQuery(hql).setMaxResult(100).setCacheable(true).list();
	 * </pre>
	 * 
	 * 调用方式如下：
	 * 
	 * <pre>
	 *        dao.createQuery(hql)
	 *        dao.createQuery(hql,arg0);
	 *        dao.createQuery(hql,arg0,arg1);
	 *        dao.createQuery(hql,new Object[arg0,arg1,arg2])
	 * </pre>
	 * 
	 * @param values
	 *            可变参数.
	 */
	public Query createQuery(String hql, Object... values) throws Exception {
		Assert.hasText(hql);
		Query query = getSession().createQuery(hql);
		for (int i = 0; i < values.length; i++) {
			query.setParameter(i, values[i]);
		}
		return query;
	}

	/**
	 * 消除与 Hibernate Session 的关联
	 * 
	 * @param entity
	 */
	public void evit(Object entity) throws Exception {
		getHibernateTemplate().evict(entity);
	}

	/**
	 * 根据hql查询,直接使用HibernateTemplate的find函数.
	 * 
	 * @param values
	 *            可变参数,见{@link #createQuery(String,Object...)}
	 */

	public List<T> find(String hql, Object... values) throws Exception {
		Assert.hasText(hql);
		return getHibernateTemplate().find(hql, values);
	}

	/**
	 * 根据属性名和属性值查询对象.
	 * 
	 * @return 符合条件的对象列表
	 */
	@SuppressWarnings("hiding")
	public <T> List<T> findBy(Class<T> entityClass, String propertyName,
			Object value) throws Exception {
		Assert.hasText(propertyName);
		return createCriteria(entityClass, Restrictions.eq(propertyName, value))
				.list();
	}

	/**
	 * 根据属性名和属性值查询对象,带排序参数.
	 */
	@SuppressWarnings("hiding")
	public <T> List<T> findBy(Class<T> entityClass, String propertyName,
			Object value, String orderBy, boolean isAsc) throws Exception {
		Assert.hasText(propertyName);
		Assert.hasText(orderBy);
		return createCriteria(entityClass, orderBy, isAsc,
				Restrictions.eq(propertyName, value)).list();
	}

	/**
	 * 根据属性名和属性值查询对象.
	 * 
	 * @return 符合条件的对象列表
	 * @see HibernateGenericDao#findBy(Class,String,Object)
	 */
	public List<T> findBy(String propertyName, Object value) throws Exception {
		return findBy(getEntityClass(), propertyName, value);
	}

	/**
	 * 根据属性名和属性值查询对象,带排序参数.
	 * 
	 * @return 符合条件的对象列表
	 * @see HibernateGenericDao#findBy(Class,String,Object,String,boolean)
	 */
	public List<T> findBy(String propertyName, Object value, String orderBy,
			boolean isAsc) throws Exception {
		return findBy(getEntityClass(), propertyName, value, orderBy, isAsc);
	}

	/**
	 * 根据属性名和属性值查询唯一对象.
	 * 
	 * @return 符合条件的唯一对象 or null if not found.
	 */
	@SuppressWarnings("hiding")
	public <T> T findUniqueBy(Class<T> entityClass, String propertyName,
			Object value) throws Exception {
		Assert.hasText(propertyName);
		return (T) createCriteria(entityClass,
				Restrictions.eq(propertyName, value)).uniqueResult();
	}

	/**
	 * 根据属性名和属性值查询单个对象.
	 * 
	 * @return 符合条件的唯一对象 or null
	 * @see HibernateGenericDao#findUniqueBy(Class,String,Object)
	 */
	public T findUniqueBy(String propertyName, Object value) throws Exception {
		return findUniqueBy(getEntityClass(), propertyName, value);
	}

	public void flush() throws Exception {
		getHibernateTemplate().flush();
	}

	/**
	 * 根据ID获取对象. 实际调用Hibernate的session.load()方法返回实体或其proxy对象. 如果对象不存在，抛出异常.
	 */
	@SuppressWarnings("hiding")
	public <T> T get(Class<T> entityClass, Serializable id) throws Exception {

		return (T) getSession().get(entityClass, id);
	}

	/**
	 * 根据ID获取对象.
	 * 
	 * @see HibernateGenericDao#getId(Class,Object)
	 */

	public T get(Serializable id) throws Exception {
		T o = (T) getSession().get(getEntityClass(), id);
		if (o == null)
			throw new ObjectRetrievalFailureException(getEntityClass(), id);
		return o;
	}

	/**
	 * 获取全部对象
	 * 
	 * @see HibernateGenericDao#getAll(Class)
	 */
	public List<T> getAll() throws Exception {
		return getAll(getEntityClass());
	}

	/**
	 * 获取全部对象.
	 */

	public List<T> getAll(Class<T> entityClass) throws Exception {
		if (_log.isDebugEnabled()) {
			_log.debug(entityClass.getName());
			System.out.println(entityClass.getName());
		}
		return getHibernateTemplate().loadAll(entityClass);
	}

	/**
	 * 获取全部对象,带排序字段与升降序参数.
	 */

	public List<T> getAll(Class<T> entityClass, String orderBy, boolean isAsc)
			throws Exception {
		Assert.hasText(orderBy);
		if (isAsc)
			return getHibernateTemplate().findByCriteria(
					DetachedCriteria.forClass(entityClass).addOrder(
							Order.asc(orderBy)));
		else
			return getHibernateTemplate().findByCriteria(
					DetachedCriteria.forClass(entityClass).addOrder(
							Order.desc(orderBy)));
	}

	/**
	 * 获取全部对象,带排序参数.
	 * 
	 * @see HibernateGenericDao#getAll(Class,String,boolean)
	 */
	public List<T> getAll(String orderBy, boolean isAsc) throws Exception {
		return getAll(getEntityClass(), orderBy, isAsc);
	}

	/**
	 * 取得entityClass.JDK1.4不支持泛型的子类可以抛开Class<T> entityClass,重载此函数达到相同效果。
	 */
	protected Class<T> getEntityClass() {
		return entityClass;
	}

	/**
	 * 取得对象的主键值,辅助函数.
	 */
	public Serializable getId(Class<T> entityClass, Object entity)
			throws Exception {
		Assert.notNull(entity);
		Assert.notNull(entityClass);
		return (Serializable) PropertyUtils.getProperty(entity,
				getIdName(entityClass));
	}

	/**
	 * 取得对象的主键名,辅助函数.
	 */
	public String getIdName(Class<T> clazz) throws Exception {
		Assert.notNull(clazz);
		ClassMetadata meta = getSessionFactory().getClassMetadata(clazz);
		Assert.notNull(meta, "Class " + clazz
				+ " not define in hibernate session factory.");
		String idName = meta.getIdentifierPropertyName();
		Assert.hasText(idName, clazz.getSimpleName()
				+ " has no identifier property define.");
		return idName;
	}

	/**
	 * 判断对象某些属性的值在数据库中是否唯一.
	 * 
	 * @param uniquePropertyNames
	 *            在POJO里不能重复的属性列表,以逗号分割 如"name,loginid,password"
	 */
	public boolean isUnique(Class<T> entityClass, Object entity,
			String uniquePropertyNames) throws Exception {
		Assert.hasText(uniquePropertyNames);
		Criteria criteria = createCriteria(entityClass).setProjection(
				Projections.rowCount());
		String[] nameList = uniquePropertyNames.split(",");
		try {
			// 循环加入唯一列
			for (String name : nameList) {
				criteria.add(Restrictions.eq(name, PropertyUtils.getProperty(
						entity, name)));
			}

			// 以下代码为了如果是update的情况,排除entity自身.

			String idName = getIdName(entityClass);

			// 取得entity的主键值
			Serializable id = getId(entityClass, entity);

			// 如果id!=null,说明对象已存在,该操作为update,加入排除自身的判断
			if (id != null)
				criteria.add(Restrictions.not(Restrictions.eq(idName, id)));
		} catch (Exception e) {
			ReflectionUtils.handleReflectionException(e);
		}
		return (Integer) criteria.uniqueResult() == 0;
	}

	/**
	 * 判断对象某些属性的值在数据库中唯一.
	 * 
	 * @param uniquePropertyNames
	 *            在POJO里不能重复的属性列表,以逗号分割 如"name,loginid,password"
	 * @see HibernateGenericDao#isUnique(Class,Object,String)
	 */
	public boolean isUnique(Object entity, String uniquePropertyNames)
			throws Exception {
		return isUnique(getEntityClass(), entity, uniquePropertyNames);
	}

	/**
	 * 分页查询函数，根据entityClass和查询条件参数创建默认的<code>Criteria</code>.
	 * 
	 * @param pageNo
	 *            页号,从1开始.
	 * @return 含总记录数和当前页数据的Page对象.
	 */
	public Page<T> pagedQuery(Class<T> entityClass, int pageNo, int pageSize,
			Criterion... criterions) throws Exception {
		Criteria criteria = createCriteria(entityClass, criterions);
		return pagedQuery(criteria, pageNo, pageSize);
	}

	/**
	 * 分页查询函数，根据entityClass和查询条件参数,排序参数创建默认的<code>Criteria</code>.
	 * 
	 * @param pageNo
	 *            页号,从1开始.
	 * @return 含总记录数和当前页数据的Page对象.
	 */
	public Page<T> pagedQuery(Class<T> entityClass, int pageNo, int pageSize,
			String orderBy, boolean isAsc, Criterion... criterions)
			throws Exception {
		Criteria criteria = createCriteria(entityClass, orderBy, isAsc,
				criterions);
		return pagedQuery(criteria, pageNo, pageSize);
	}

	/**
	 * 分页查询函数，使用已设好查询条件与排序的<code>Criteria</code>.
	 * 
	 * @param pageNo
	 *            页号,从1开始.
	 * @return 含总记录数和当前页数据的Page对象.
	 */

	public Page<T> pagedQuery(Criteria criteria, int pageNo, int pageSize)
			throws Exception {
		Assert.notNull(criteria);
		Assert.isTrue(pageNo >= 1, "pageNo should start from 1");
		CriteriaImpl impl = (CriteriaImpl) criteria;

		// 先把Projection和OrderBy条件取出来,清空两者来执行Count操作
		Projection projection = impl.getProjection();
		List<CriteriaImpl.OrderEntry> orderEntries;
		try {
			orderEntries = (List) BeanUtils.forceGetProperty(impl,
					"orderEntries");
			BeanUtils.forceSetProperty(impl, "orderEntries", new ArrayList());
		} catch (Exception e) {
			throw new InternalError(" Runtime Exception impossibility throw ");
		}

		// 执行查询
		int totalCount = (Integer) criteria.setProjection(
				Projections.rowCount()).uniqueResult();

		// 将之前的Projection和OrderBy条件重新设回去
		criteria.setProjection(projection);
		if (projection == null) {
			criteria.setResultTransformer(CriteriaSpecification.ROOT_ENTITY);
		}

		try {
			BeanUtils.forceSetProperty(impl, "orderEntries", orderEntries);
		} catch (Exception e) {
			throw new InternalError(" Runtime Exception impossibility throw ");
		}

		// 返回分页对象
		if (totalCount < 1)
			return new Page<T>();

		int startIndex = Page.getStartOfPage(pageNo, pageSize);
		// .setFirstResult(startIndex)
		criteria.setFirstResult(startIndex);
		criteria.setMaxResults(pageSize);
		List<T> list = criteria.list();
		return new Page<T>(startIndex, totalCount, pageSize, list);
	}

	/**
	 * 分页查询函数，使用hql.
	 * 
	 * @param pageNo
	 *            页号,从1开始.
	 */

	public Page<T> pagedQuery(String hql, int pageNo, int pageSize,
			Object... values) throws Exception {
		Assert.hasText(hql);
		Assert.isTrue(pageNo >= 1, "pageNo should start from 1");
		// Count查询
		String countQueryString = " select count (*) "
				+ removeSelect(removeOrders(hql));
		List<T> countlist = getHibernateTemplate().find(countQueryString,
				values);
		int totalCount = Integer.parseInt(String.valueOf(countlist.get(0)));

		if (totalCount < 1)
			return new Page<T>();
		// 实际查询返回分页对象
		int startIndex = Page.getStartOfPage(pageNo, pageSize);
		Query query = createQuery(hql, values);
		List<T> list = query.setFirstResult(startIndex).setMaxResults(pageSize)
				.list();

		return new Page(startIndex, totalCount, pageSize, list);
	}

	/**
	 * 删除对象.
	 */
	public void remove(Object o) throws Exception {
		getHibernateTemplate().delete(o);
	}

	/**
	 * 根据ID删除对象.
	 */
	public void removeById(Class<T> entityClass, Serializable id)
			throws Exception {
		remove(get(entityClass, id));
	}

	/**
	 * 根据ID移除对象.
	 * 
	 * @see HibernateGenericDao#removeById(Class,Serializable)
	 */
	public void removeById(Serializable id) throws Exception {
		removeById(getEntityClass(), id);
	}

	/**
	 * 保存对象.
	 */
	public void saveObject(Object obj) throws Exception {
//		Session session = getSession();
//		session.setFlushMode(FlushMode.COMMIT);
//		session.update(obj);
		getHibernateTemplate().update(obj);
	}

	@Override
	public void batchHQL(final String hql, final Object[] values)
			throws Exception {
		// TODO Auto-generated method stub
		getHibernateTemplate().bulkUpdate(hql, values);
	}

	@Override
	public List<HashMap<String, String>> findCommnonDataBeanExt(
			String tableName, String id) throws Exception {
		// TODO Auto-generated method stub
		Session session = getSession();
		Connection conn = ((SessionImplementor) session).getJDBCContext()
				.borrowConnection();
		Statement ps = conn.createStatement();
		StringBuilder sql = new StringBuilder();
		sql.append("select * from ");
		sql.append(tableName);
		sql.append(" where  id='");
		sql.append(id.replaceAll("'", "''"));
		sql.append("' ");
		if (logger.isDebugEnabled()) {
			System.out.println(sql);
			logger.debug(sql);
		}
		ResultSet rs = ps.executeQuery(sql.toString());
		List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
		while (rs.next()) {
			HashMap<String, String> map = new HashMap<String, String>();
			ResultSetMetaData rsmd = rs.getMetaData();
			int icount = rsmd.getColumnCount();
			for (int i = 1; i <= icount; i++) {
				int t = rsmd.getColumnType(i);
				if (Types.DATE == t || t == Types.TIMESTAMP) {
					SimpleDateFormat dateFormat = new SimpleDateFormat(
							"yyyy-MM-dd");
					if (!(rs.getDate(rsmd.getColumnName(i)) == null)) {
						map.put(rsmd.getColumnName(i), dateFormat.format(rs
								.getDate(rsmd.getColumnName(i))));
					} else {
						map.put(rsmd.getColumnName(i), null);
					}
				} else if (t == Types.TIME) {
					SimpleDateFormat dateFormat = new SimpleDateFormat("HH:mm");
					// System.out.println(rsmd.getColumnName(i));
					// System.out.println(rs.getTime(rsmd.getColumnName(i)));
					if (!(rs.getTime(rsmd.getColumnName(i)) == null)) {
						map.put(rsmd.getColumnName(i), dateFormat.format(rs
								.getTime(rsmd.getColumnName(i))));
					} else {
						map.put(rsmd.getColumnName(i), null);
					}
				} else if (t == Types.FLOAT || t == Types.DECIMAL
						|| t == Types.DOUBLE) {
					DecimalFormat myformat = new DecimalFormat("#0.00");
					String tdouble = myformat.format(rs.getDouble(rsmd
							.getColumnName(i)));
					map.put(rsmd.getColumnName(i), tdouble);
				} else {
					map.put(rsmd.getColumnName(i), rs.getString(rsmd
							.getColumnName(i)));
				}

			}
			list.add(map);
		}
		if (rs != null) {
			rs.close();
			rs = null;
		}
		if (ps != null) {
			ps.close();
			ps = null;
		}
		if (conn != null) {
			conn.close();
			conn = null;
		}
		return list;
	}

	@Override
	public void saveCommnonDataBeanExt(String sql) throws Exception {
		// TODO Auto-generated method stub
		Session session = getSession();
		Connection conn = ((SessionImplementor) session).getJDBCContext()
				.borrowConnection();
		Statement ps = conn.createStatement();
		if (logger.isDebugEnabled()) {
			System.out.println(sql);
			logger.debug(sql);
		}
		ps.executeUpdate(sql);
		if (ps != null) {
			ps.close();
			ps = null;
		}
		if (conn != null) {
			conn.close();
			conn = null;
		}
	}

	@Override
	public boolean getHasCommnonDataBeanExt(String tableName, String id)
			throws Exception {
		// TODO Auto-generated method stub
		Session session = getSession();
		Connection conn = ((SessionImplementor) session).getJDBCContext()
				.borrowConnection();
		Statement ps = conn.createStatement();
		StringBuilder sql = new StringBuilder();
		sql.append("select ID from ");
		sql.append(tableName);
		sql.append(" where ID='");
		sql.append(id);
		sql.append("'");
		if (logger.isDebugEnabled()) {
			System.out.println(sql);
			logger.debug(sql);
		}
		ResultSet rs = ps.executeQuery(sql.toString());
		String tid = null;
		while (rs.next()) {
			tid = rs.getString("ID");
		}
		if (rs != null) {
			rs.close();
			rs = null;
		}
		if (ps != null) {
			ps.close();
			ps = null;
		}
		if (conn != null) {
			conn.close();
			conn = null;
		}

		boolean iflag = false;
		if (tid != null && id.equals(tid)) {
			iflag = true;
		}
		return iflag;
	}
}
